package com.alianzamedica.models;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.objectsearch.sqlsearch.ObjectSearch;
import org.w3c.dom.Document;

import com.alianzamedica.businessobject.Drug;
import com.alianzamedica.businessobject.MedicRecets;
import com.alianzamedica.businessobject.MedicReportObject;
import com.alianzamedica.businessobject.SalesByMedicObject;
import com.alianzamedica.connection.ConnectionImpl;
import com.alianzamedica.tools.Enviroment;

/**
 * @author Carlos
 * 
 */
public class ReportModel {

	private PrintWriter out;

	/**
	 * constructor que agrega un printwriter a la salida
	 * 
	 * @param out
	 *            salida del sistema .
	 */
	public ReportModel(PrintWriter out) {
		this.out = out;
	}

	/**
	 * constructor por defecto para el manejo de los reportes.
	 */
	public ReportModel() {

	}

	// consultas por medico
	/**
	 * obtiene las consultas por medico
	 * 
	 * @param doctorId
	 * @throws Exception
	 */
	@Deprecated
	public void getPrescriptionReport(int doctorId) throws Exception {
		ConnectionImpl impl = new ConnectionImpl();
		StringBuffer table = new StringBuffer("<table id=\"report\" >");
		String sql = "select DOCTOR_FULLNAME,DOCTOR_UNIVERSITY,DOCTOR_SPECIALITY,DOCTOR_CELL,"
				+ " DOCTOR_CERTIFICATION_NUMBER,PRESCRIPTION_NAME,"
				+ " date_format( PRESCRIPTION_DATE , '%d/%m/%Y')   PRESCRIPTION_DATE ,PATIENT_FIRSTNAME,"
				+ " PATIENT_LASTNAME,PATIENT_AGE,PATIENT_DIAGNOSTIC,PATIENT_SEX,PATIENT_WEIGHT"
				+ " from prescription"
				+ " join patient"
				+ " on ( prescription.PATIENT_ID  = patient.PATIENT_ID )"
				+ " join doctor"
				+ " on ( doctor.DOCTOR_ID = prescription.DOCTOR_ID )"
				+ " where prescription.DOCTOR_ID = ? ";
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			String nl = "\n";
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			rs = psmt.executeQuery();
			ResultSetMetaData metadata = rs.getMetaData();
			int cols = metadata.getColumnCount();

			table.append("<tr>");
			for (int i = 0; i < cols; i++) {
				String head = metadata.getCatalogName(i + 1);
				String h = createHeader(head);
				table.append(h);
			}
			table.append("</tr>").append(nl);
			while (rs.next()) {
				table.append("<tr>");
				for (int i = 0; i < cols; i++) {
					String data = rs.getString(i);
					String cell = createCell(data);
					table.append(cell);
				}
				table.append("</tr>").append(nl);
			}
			table.append("</table>").append(nl);
			this.out.print(table);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (psmt != null) {
				try {
					psmt.close();
				} catch (Exception e) {
				}
			}
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
	}

	/*
	 * "select DOCTOR_FULLNAME,DOCTOR_UNIVERSITY,DOCTOR_SPECIALITY,DOCTOR_CELL,"+
	 * " DOCTOR_CERTIFICATION_NUMBER,PRESCRIPTION_NAME,"+
	 * " date_format( PRESCRIPTION_DATE , '%d/%m/%Y')   PRESCRIPTION_DATE ,PATIENT_FIRSTNAME,"
	 * +
	 * " PATIENT_LASTNAME,PATIENT_AGE,PATIENT_DIAGNOSTIC,PATIENT_SEX,PATIENT_WEIGHT"
	 * + " from prescription"+ " join patient"+
	 * " on ( prescription.PATIENT_ID  = patient.PATIENT_ID )"+ " join doctor"+
	 * " on ( doctor.DOCTOR_ID = prescription.DOCTOR_ID )"+
	 * " where prescription.DOCTOR_ID = ? ";
	 */

	/**
	 * regresa un arraylist con el reporte de los medicos.
	 * 
	 * @param doctorId
	 *            id del doctor
	 * @param pageNum
	 *            numero de pagina
	 * @param length
	 *            longitud del segmento
	 * @return lista de reporte de medico
	 * @throws Exception
	 *             si ocurre error.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<MedicReportObject> getMedicReport(int doctorId,
			int pageNum, int length) throws Exception {
		ArrayList<MedicReportObject> list = null;
		int page = (pageNum - 1) * length;
		String sql = "select DOCTOR_FULLNAME,DOCTOR_UNIVERSITY,DOCTOR_SPECIALITY,DOCTOR_CELL,"
				+ " DOCTOR_CERTIFICATION_NUMBER,PRESCRIPTION_NAME,"
				+ " PRESCRIPTION_DATE  PRESCRIPTION_DATE ,PATIENT_FIRSTNAME,"
				+ " PATIENT_LASTNAME, PATIENT_AGE,PATIENT_DIAGNOSTIC,PATIENT_SEX,PATIENT_WEIGHT "
				+ " from prescription"
				+ " join patient"
				+ " on ( prescription.PATIENT_ID  = patient.PATIENT_ID )"
				+ " join doctor"
				+ " on ( doctor.DOCTOR_ID = prescription.DOCTOR_ID )"
				+ " where prescription.DOCTOR_ID = ? limit ? , ? ";
		PreparedStatement psmt = null;
		ResultSet rs = null;
		ConnectionImpl impl = new ConnectionImpl();
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		try {
			ObjectSearch search = new ObjectSearch(doc, ConnectionImpl.class
					.getName());
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			psmt.setInt(2, page);
			psmt.setInt(3, length);
			list = search.customQuery(psmt, con, MedicReportObject.class);
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(rs);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	/**
	 * obtiene las paginas totales del reporte medico.
	 * 
	 * @param medicId
	 *            the medic id.
	 * @param size
	 *            size of the report.
	 * @return the number of pages from report.
	 */
	public int getTotalPagesFromMedicReport(int medicId, int size) {
		String sql = "select ceil( count(*) / " + size + " )   "
				+ " from prescription" + " join patient"
				+ " on ( prescription.PATIENT_ID  = patient.PATIENT_ID )"
				+ " join doctor"
				+ " on ( doctor.DOCTOR_ID = prescription.DOCTOR_ID )"
				+ " where prescription.DOCTOR_ID = ? ";
		int totalPages = 0;
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, medicId);
			rs = psmt.executeQuery();
			while (rs.next()) {
				totalPages = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(rs);
			try {
				impl.close();
			} catch (Exception e2) {
			}
		}
		return totalPages;
	}

	/**
	 * regresa el medicamento mas vendido.
	 * 
	 * @return medicamento mas vendido.
	 * @throws Exception
	 *             si ocurre error.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getMostSellDrug() throws Exception {
		String sql = "select * from drug where drug_id in "
				+ "( select drug_id from (select drug_id,sum(quantity) "
				+ "as qsold from prescription_detail  group by drug_id) "
				+ "as quantities_sold where qsold in ( select max(qsold) "
				+ "from (select drug_id,sum(quantity) as qsold from "
				+ "prescription_detail  group by drug_id)  "
				+ "as quantities_sold ) )";
		// Drug drug = null;
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ConnectionImpl impl = new ConnectionImpl();
		ObjectSearch search = new ObjectSearch(doc, impl);
		PreparedStatement psmt = null;
		ArrayList<Drug> list = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			list = search.customQuery(psmt, con, Drug.class);
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		// Iterator<Drug> iterator = list.iterator();
		// while( iterator.hasNext() ){
		// drug = iterator.next();
		// }
		return list;
	}

	private void closeGracefully(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
			}
		}
	}

	private void closeGracefully(PreparedStatement psmt) {
		if (psmt != null) {
			try {
				psmt.close();
			} catch (SQLException e) {
			}
		}
	}

	@SuppressWarnings("unused")
	private void closeGracefully(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	/**
	 * regresa un arreglo de objetos con la lista de ventas por medico.
	 * 
	 * @param doctorId
	 *            id del doctor
	 * @return arreglo de ventas por medico
	 * @throws Exception
	 *             si ocurre error
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<SalesByMedicObject> getMedicSalesReport(int doctorId)
			throws Exception {
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ObjectSearch search = new ObjectSearch(doc,
				"com.alianzamedica.connection.ConnectionImpl");
		ArrayList<SalesByMedicObject> list = null;
		ConnectionImpl impl = new ConnectionImpl();
		String sql = "SELECT DRUG_NAME ,SUM( QUANTITY )  QUANTITY_SOLD "
				+ " FROM prescription_detail PD JOIN "
				+ " prescription P ON (P.PRESCRIPTION_ID "
				+ " = PD.PRESCRIPTION_ID) JOIN "
				+ " drug D ON (D.DRUG_ID = PD.DRUG_ID) "
				+ " WHERE DOCTOR_ID = ? GROUP BY PD.DRUG_ID";
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			System.out.println(psmt.toString());
			list = search.customQuery(psmt, con, SalesByMedicObject.class);
		} finally {
			closeGracefully(rs);
			closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	/**
	 * obtiene las ventas realizadas por medico.
	 * 
	 * @param doctorId
	 *            id del doctor.
	 * @throws Exception
	 *             si ocurre algun error.
	 */
	@Deprecated
	public void getSalesByMedic(int doctorId) throws Exception {
		ConnectionImpl impl = new ConnectionImpl();
		String sql = "select drug_name ,sum( quantity )  quantity_sold "
				+ " from prescription_detail pd join "
				+ " prescription p on p.prescription_id "
				+ " = pd.prescription_id join "
				+ " drug d on d.drug_id = pd.drug_id "
				+ " where doctor_id = ? group by pd.drug_id";
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			StringBuffer table = new StringBuffer();
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			rs = psmt.executeQuery();
			ResultSetMetaData metadata = rs.getMetaData();
			int cols = metadata.getColumnCount();
			table.append("<table id=\"report\" >\n");
			table.append("<tr>");
			for (int i = 0; i < cols; i++) {
				String col = metadata.getCatalogName(i + 1);
				String head = createHeader(col);
				table.append(head);
			}
			table.append("</tr>\n");
			while (rs.next()) {
				table.append("<tr>");
				for (int i = 0; i < cols; i++) {
					String value = rs.getString(i + 1);
					String cell = this.createCell(value);
					table.append(cell);
				}
				table.append("<tr>\n");
			}
			table.append("</table>");
			out.print(table.toString());
		} finally {
			closeGracefully(rs);
			closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}

	}

	private String createCell(String data) {
		return "<td>" + data + "</td>";
	}

	private String createHeader(String head) {
		return "<th>" + head + "</th>";
	}

	/**
	 * regresa las recetas medicas creadas para el sistema
	 * 
	 * @param medicId
	 *            id del medico que crea las recetas
	 * @return lista de recetas creadas por el medico
	 * @throws Exception
	 *             si ocurre error.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<MedicRecets> getRecets(int medicId) throws Exception {
		String sql = " SELECT PRESCRIPTION_ID , " + " PRESCRIPTION_DATE , "
				+ " (select PATIENT_FULLNAME FROM patient p "
				+ " where p.PATIENT_ID = pr.PATIENT_ID ) "
				+ " as PATIENT_FULLNAME  FROM prescription pr"
				+ " where DOCTOR_ID = ? order by  PRESCRIPTION_DATE desc ";
		ConnectionImpl impl = new ConnectionImpl();
		ArrayList<MedicRecets> list = null;
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ObjectSearch search = new ObjectSearch(doc, impl);
		try {
			impl.open();
			Connection con = impl.getConnection();
			PreparedStatement psmt = con.prepareStatement(sql);
			psmt.setInt(1, medicId);
			list = search.customQuery(psmt, con, MedicRecets.class);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

}
